﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using BoutiquePOS.DAL.DAO;

namespace BoutiquePOS.DAL.Gateway
{
    public class PurchaseReturnDtlsGateway:DbGateway
    {

        public string SavePurchaseReturn(PurchaseReturn aPurchaseReturn)
        {
            try
            {
                ConnectionObj.Open();
                string query = String.Format("INSERT INTO PosPurchaseReturn VALUES(@purchaseId,@purchaseReturnDate,@totalNetAmount)");
                CommandObj.CommandText = query;
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@purchaseId", SqlDbType.Int);
                CommandObj.Parameters["@purchaseId"].Value = aPurchaseReturn.PurchaseId;
                CommandObj.Parameters.Add("@purchaseReturnDate", SqlDbType.VarChar);
                CommandObj.Parameters["@purchaseReturnDate"].Value = aPurchaseReturn.PurchaseReturnDate;
                CommandObj.Parameters.Add("@totalNetAmount", SqlDbType.Decimal);
                CommandObj.Parameters["@totalNetAmount"].Value = aPurchaseReturn.TotalNetAmount;
                CommandObj.ExecuteNonQuery();
                ConnectionObj.Close();
            }
            catch (Exception ex)
            {
                throw new Exception("Error in data");
            }
            return "Save success";
        }

        public string SavePurchaseReturnDtls(int id, List<PurchaseReturnDtls> aPurchaseReturnDtlses)
        {
            foreach (PurchaseReturnDtls aPurchaseReturnDtls in aPurchaseReturnDtlses)
            {
                try
                {
                    ConnectionObj.Open();
                    string query1 = String.Format("INSERT INTO PosPurchaseReturnDtls VALUES(@purchaseReturnId,@purchaseId,@productId,@returnQty,@returnFreeQty)");
                    CommandObj.CommandText = query1;
                    CommandObj.Parameters.Clear();
                    CommandObj.Parameters.Add("@purchaseReturnId", SqlDbType.Int);
                    CommandObj.Parameters["@purchaseReturnId"].Value = id;
                    CommandObj.Parameters.Add("@purchaseId", SqlDbType.Int);
                    CommandObj.Parameters["@purchaseId"].Value = aPurchaseReturnDtls.PurchaseId;
                    CommandObj.Parameters.Add("@productId", SqlDbType.Int);
                    CommandObj.Parameters["@productId"].Value = aPurchaseReturnDtls.ProductId;
                    CommandObj.Parameters.Add("@returnQty", SqlDbType.Int);
                    CommandObj.Parameters["@returnQty"].Value = aPurchaseReturnDtls.ReturnQty;
                    CommandObj.Parameters.Add("@returnFreeQty", SqlDbType.Int);
                    CommandObj.Parameters["@returnFreeQty"].Value = aPurchaseReturnDtls.ReturnFreeQty;
                    CommandObj.ExecuteNonQuery();
                    ConnectionObj.Close();
                }
                catch (Exception ex)
                {
                    throw new Exception("Error in data");
                }
            }
            return "Save success1";
        }

        internal int PurchaseReturnId(int purchaseReturnId)
        {
            int id1 = 0;
            try
            {
                string search = String.Format("Select * FROM PosPurchaseReturn WHERE purchaseId='{0}'", purchaseReturnId);
                ConnectionObj.Open();
                CommandObj.CommandText = search;
                SqlDataReader reader = CommandObj.ExecuteReader();
                while (reader.Read())
                {
                    id1 = Convert.ToInt32(reader[0]);
                }
                return id1;
            }
            catch (Exception exception)
            {
                throw new Exception("Could not Found Data", exception);
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
        }

        public int ReturnPurchaseId(string purchaseNo)
        {
            int id = 0;
            try
            {
                string search = String.Format("Select purchaseId FROM PharPurchase WHERE purchaseNo='{0}'", purchaseNo);
                ConnectionObj.Open();
                CommandObj.CommandText = search;
                SqlDataReader reader = CommandObj.ExecuteReader();
                while (reader.Read())
                {
                    id = Convert.ToInt32(reader[0]);
                }
                return id;
            }
            catch (Exception exception)
            {
                throw new Exception("Could not found data", exception);
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
        }

        //internal int ReturnPurchaseReturnId(int purchaseReturnId)
        //{
        //    int id1 = 0;
        //    try
        //    {
        //        string search = String.Format("Select * FROM PharPurchaseReturn WHERE PurchaseId='{0}'", purchaseReturnId);
        //        ConnectionObj.Open();
        //        CommandObj.CommandText = search;
        //        SqlDataReader reader = CommandObj.ExecuteReader();
        //        while (reader.Read())
        //        {
        //            id1 = Convert.ToInt32(reader[0]);
        //        }
        //        return id1;
        //    }
        //    catch (Exception exception)
        //    {
        //        throw new Exception("Could not Found SalesDtls", exception);
        //    }
        //    finally
        //    {
        //        if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
        //        {
        //            ConnectionObj.Close();
        //        }
        //    }
        //}
        public List<PurchaseDtls> GetAllPurchaseProduct(int purchaseId)
        {
            List<PurchaseDtls> purchaseDtlses = new List<PurchaseDtls>();
            try
            {               
                string search = String.Format("SELECT PharPurchaseDtls.purchaseId, PharPurchaseDtls.productId, PharPurchaseDtls.purchaseQty, PharPurchaseDtls.purchaseFreeQty, PharPurchaseDtls.tradePrice, PharPurchaseDtls.vat, PharPurchaseDtls.discountPer, PharProduct.productBrandName FROM PosPurchaseDtls INNER JOIN PosProduct ON PosPurchaseDtls.productId = PosProduct.productId and PosPurchaseDtls.purchaseId='{0}'", purchaseId);
                ConnectionObj.Open();
                CommandObj.CommandText = search;
                SqlDataReader reader = CommandObj.ExecuteReader();
                while (reader.Read())
                {
                    PurchaseDtls purchaseDtls = new PurchaseDtls();
                    Product product = new Product();
                    purchaseDtls.AProduct = product;
                    purchaseDtls.PurchaseId = Convert.ToInt32(reader[0]);
                    purchaseDtls.AProduct.ProductId = Convert.ToInt32(reader[1]);
                    purchaseDtls.AProduct.ProductBrandName = reader[7].ToString();

                    purchaseDtls.PurchaseQty = Convert.ToInt32(reader[2]);
                    purchaseDtls.TradePrice = Convert.ToDecimal(reader[4]);
                    purchaseDtls.Vat = Convert.ToDecimal(reader[5]);
                    purchaseDtls.DiscountPer = Convert.ToDecimal(reader[6]);
                    purchaseDtls.PurchaseFreeQty = Convert.ToInt32(reader[3]);

                    purchaseDtls.ReturnQty = 0;
                    purchaseDtls.ReturnFreeQty = 0;
                    purchaseDtlses.Add(purchaseDtls);
                }
            }
            catch (Exception exception)
            {
                throw new Exception("Could not Found SalesDtls", exception);
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
            return purchaseDtlses;

        }



    }
}